# mysql 数据库
import pymysql
# 创建mysql连接
conn = pymysql.Connect(
    host='192.168.88.100',
    port=3306,
    user='root',
    password='123456',
    db='tedu',
    charset='utf8'
)

# 创建游标，用于执行sql命令
cur = conn.cursor()
# 执行sql命令
# 创建表
create_sql = '''
    create table dept(
        did int,
        dname varchar(16)
    )
'''
# 执行命令
cur.execute(create_sql)

# 插入数据
insert_sql = 'insert into dept values(1, "研发部")'
cur.execute(insert_sql)
# 将命令设置成模板
insert_sql = 'insert into dept values(%s, %s)'
# 以模板执行一次，值为元组
cur.execute(insert_sql, (3, '运维部'))
# 执行多次，值为列表中嵌套元组
cur.executemany(insert_sql, [(4, '测试部'), (5, '设计部'), (6, '运营部')])

# 查询数据
select_sql = 'select * from dept'
cur.execute(select_sql)
# 通过获取查询的数据，元组格式，存在指针
# 取出一条
one = cur.fetchone()
print(one)
# 取出多条
many = cur.fetchmany(2)
print(many)
# 取出所有
all = cur.fetchall()
print(all)

# 修改数据
update_sql = 'update dept set dname=%s where did=%s'
cur.execute(update_sql, ('财务部', 1))
cur.executemany(update_sql, [('人力部', 2), ('后勤部', 3)])

# 删除数据
delete_sql = 'delete from dept where did=%s'
cur.execute(delete_sql, (2, ))
cur.executemany(delete_sql, [(1, ), (3, )])

# 提交事务：只要数据发生变化，必须提交事务
conn.commit()
# 关闭游标
cur.close()
# 关闭连接
conn.close()
